IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PopulateExpenseDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PopulateExpenseDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--------------------------------------------------------------------
--------------------------------------------------------------------
--Modifications:
--09-04-2007	Performance improvements: introduce (nolock)
--------------------------------------------------------------------
CREATE Procedure [dbo].[PopulateExpenseDetails]
/*
**	Populates the expense details behind all GL entries for a given
**	fiscal period.  The details are in the form of vouchers and expense GL
**  entries.  These are created once at the beginning and are added to 
**  at every month end close.
exec populateExpenseDetails '1/1/2004','12/31/2004'
**
**  This routine removes indexes on the table prior to creating rows.
**  It then replaces the indexes once it completes.
**  
**  Date    	Name        Description
**  --------------------------------------------------------------------
**	2/2/2006				Created
**	Parameter			Type				Param	Notes 
*/
(
	@transactionTypeFrom	datetime = null
	, @transactionTypeTo	datetime = null
	, @fundNumber			varchar(6) = null
)
as

Begin

set @transactionTypeFrom = IsNull(@transactionTypeFrom,'1/1/1900')
set @transactionTypeTo = IsNull(@transactionTypeTo,'09/30/2999')

declare @fundId int
if @fundnumber is not null select @fundId = id from agrfund where fundnumber = @fundNumber

IF OBJECT_ID('tempdb..#TempFundCollection') is not null drop table #TempFundCollection
create table #TempFundCollection (FundID int not null)

if @fundnumber is not null  begin
	Insert into #TempFundCollection
	Select distinct jt.fundid from finJournalTransaction_Src jt 
	where jt.transactiondate between @transactionTypeFrom and @transactionTypeTo
			and jt.FundId = @FundId
end
else begin
	Insert into #TempFundCollection
	Select distinct jt.fundid from finJournalTransaction_Src jt 
	where jt.transactiondate between @transactionTypeFrom and @transactionTypeTo
end

if OBJECT_ID('tempdb..#tjt') is not null drop table #tjt

if OBJECT_ID('tempdb..#final') is not null drop table #final
create table #final(
		BusinessUnit varchar(5),
		JournalID varchar(10),
		JournalLine int,
		CategoryId int,
		AccountId int,
		AccountNumber varchar(6),
		UnpostSeqNumber int,
		OrderDate datetime,
		InvoiceDate datetime,
		AccountingDate datetime,
		OrderTypeId int,
		OrderType varchar(3),
		OrderNumber varchar(10),
		PSVoucher varchar(8),
		InvoiceNumber varchar(16),
		RequisitionNumber varchar(30),
		VendorName  varchar(64),
		RequestorName  varchar(50),
		FundId integer,
		FundNumber varchar(6),
		OrderAmount money,
		ExpensedToDate money,
		EncumbranceAmount money,
		InvoiceAmount money,
		Source varchar(5))

/*
	GET THE AP JOURNAL ENTRIES FOR THE PERIOD.
*/
select  jt.FundId,jt.businessunit,acc.FinCategoryId as CategoryId,jt.AccountId,acc.Number as AccountNumber
	,jt.FundNumber,jt.JournalId,jt.JournalLine,jt.Amount
	,jt.UnpostSeqnumber,jt.Ledger,jt.Source,jt.transactiondate,jt.entrydescription, cast(0 as money) DetailAmount
into #tjt
from finjournaltransaction_src jt
	inner join #TempFundCollection t on t.fundid = jt.fundid
	inner join finaccount acc on acc.id = jt.accountid
where jt.source = 'AP'
	and jt.transactiondate between @transactionTypeFrom and @transactionTypeTo

create index JournalId on #tjt (businessunit,journalid,JournalLine,transactionDate)

/*
	INSERT AP DETAILS INTO #FINAL BY JOINING TO THE AP JOURNALS.
*/
insert into #final (BusinessUnit, JournalID, JournalLine, CategoryId, AccountId, AccountNumber, UnpostSeqNumber, OrderDate, InvoiceDate, 
					AccountingDate, OrderTypeId, OrderType ,OrderNumber,PSVoucher, InvoiceNumber, RequisitionNumber, VendorName, RequestorName, 
					FundId, FundNumber, OrderAmount,ExpensedToDate, EncumbranceAmount, InvoiceAmount, Source)
select 
		val.BusinessUnitGL ,
		val.JournalID,
		val.JournalLine,
		acc.finCategoryId,
		acc.Id,
		acc.Number,
		val.UnpostSeq,
		case when p.pspoid is null then vh.VoucherDate else p.PODate end, -- order date
		vh.InvoiceDate, -- invoice date
		val.JournalDate, -- accounting date
		case when p.pspoid is null then 2 else 1 end,
		case when p.pspoid is null then 'AP' else 'PO' end,
		case when p.pspoid is null then val.PSVoucherId else val.PSPOID end,
		val.PSVoucherId ,
		vh.InvoiceNumber,
		IsNull(p.POref,''),
		left(v.VendorName,20),
		IsNull(p.RequestorName,''),
		val.FundId,
		t.FundNumber,
		sum(val.MerchandiseAmount)		as  OrderAmount,
		sum(val.MerchandiseAmount)		as ExpensedToDate,
		0							as EncumbranceAmount,
		0							as InvoiceAmount,
		null
from purvoucheraccountingline_src val
inner join finaccount acc on acc.Number = val.account
inner join #tjt t on t.journalid = val.journalid and t.journalline = val.journalline
					and t.transactiondate = val.journaldate and t.businessunit = val.businessunitgl
					and t.unpostSeqNumber = val.unpostSeq
					and t.fundid = val.fundid and t.accountid = acc.id --and val.distAccountType = 'DST'
inner join purvoucherheader_src vh on vh.businessunit = val.businessunit and vh.psvoucherid = val.psvoucherid
inner join purvendor v on v.id = vh.VendorId and v.setid = 'PRTNS'
left outer join PurPOHeader_Src p on p.businessunit = val.businessunit and p.PSPOID = val.PSPOID
group by val.BusinessUnitGL ,
		val.JournalID,
		val.JournalLine,
		acc.FinCategoryId,
		acc.Id,
		acc.Number,
		val.UnpostSeq,
		p.PODate, -- Purchase order date
		vh.VoucherDate, -- order date
		vh.InvoiceDate, -- invoice date
		val.JournalDate,
		p.PSPOID,
		val.PSPOID,
		val.PSVoucherId,
		vh.InvoiceNumber,
		IsNull(p.POref,''),
		left(v.VendorName,20),
		IsNull(p.RequestorName,''),
		val.FundId,
		t.FundNumber

create index JournalId on #final (businessunit,journalid,JournalLine,accountingDate)

/*
	SUM VOUCHER DETAILS TO THE CORRESPONDING JOURNAL TRANSACTIONS.
*/
update t set detailAmount = (select sum(IsNull(f1.ExpensedToDate,0)) from #final f1 where 
					t.journalid = f.journalid and t.journalline = f1.journalline
					and t.transactiondate = f1.accountingdate and t.businessunit = f1.businessunit
					and t.fundid = f1.fundid and t.accountid = f1.accountid and t.unpostSeqNumber = f1.unpostSeqNumber)
from #tjt t 
inner join #final f on 
t.journalid = f.journalid and t.journalline = f.journalline
					and t.transactiondate = f.accountingdate and t.businessunit = f.businessunit
					and t.fundid = f.fundid and t.accountid = f.accountid and t.unpostSeqNumber = f.unpostSeqNumber

drop Index #final.JournalId

/*
	MATCH THE VOUCHER DETAILS WITH THE JOURNAL TRANSACTIONS AND CREATE A JOURNAL ENTRY FOR ANY DIFFERENCES
*/
insert into #final (BusinessUnit, JournalID, JournalLine, CategoryId, AccountId, AccountNumber, UnpostSeqNumber, OrderDate, InvoiceDate, 
					AccountingDate, OrderTypeId, OrderType ,OrderNumber,PSVoucher, InvoiceNumber, RequisitionNumber, VendorName, RequestorName, 
					FundId, FundNumber, OrderAmount,ExpensedToDate, EncumbranceAmount, InvoiceAmount, Source)
select jt.BusinessUnit,jt.JournalId,jt.JournalLine,jt.CategoryId,jt.AccountId,jt.AccountNumber,jt.UnpostSeqNumber,jt.transactiondate
		,jt.transactiondate,jt.transactiondate,3,'JE',jt.JournalId,jt.JournalLine,'','',''
		,'',jt.FundId,jt.FundNumber,jt.Amount-jt.detailamount,jt.Amount-jt.detailamount,0
		,0,'CRX'
from #tjt jt 
where abs(amount - detailamount)>.5 order by amount

/*
	GET THE NON-AP JOURNAL ENTRIES FOR THE PERIOD.
*/
insert into #final (BusinessUnit, JournalID, JournalLine, CategoryId, AccountId, AccountNumber, UnpostSeqNumber, OrderDate, InvoiceDate, AccountingDate, 
					OrderTypeId, OrderType ,OrderNumber,PSVoucher, InvoiceNumber, RequisitionNumber, VendorName, RequestorName, 
					FundId, FundNumber, OrderAmount,ExpensedToDate, EncumbranceAmount, InvoiceAmount, Source)
select jt.BusinessUnit,jt.JournalId,jt.JournalLine,acc.FinCategoryId,jt.AccountId,acc.Number,jt.UnpostSeqNumber,jt.transactiondate
		,jt.transactiondate,jt.transactiondate,3,'JE',jt.JournalId,jt.JournalLine,'','',''
		,'',jt.FundId,jt.FundNumber,jt.Amount,jt.Amount,0,0,jt.Source
from finjournaltransaction_src jt
	inner join #TempFundCollection t on t.fundid = jt.fundid
	inner join finaccount acc on acc.id = jt.accountid
where jt.source <> 'AP'
	and jt.transactiondate between @transactionTypeFrom and @transactionTypeTo

if OBJECT_ID('dbo.ExpenseDetails') is null begin
	create table dbo.ExpenseDetails(
		Id int Identity,
		BusinessUnit varchar(5),
		JournalID varchar(10),
		JournalLine int,
		CategoryId int,
		AccountId int,
		AccountNumber varchar(6),
		UnpostSeqNumber int,
		OrderDate datetime,
		InvoiceDate datetime,
		AccountingDate datetime,
		OrderTypeId int,
		OrderType varchar(3),
		OrderNumber varchar(10),
		PSVoucher varchar(8),
		InvoiceNumber varchar(16),
		RequisitionNumber varchar(30),
		VendorName  varchar(64),
		RequestorName  varchar(50),
		FundId integer,
		FundNumber varchar(6),
		OrderAmount money,
		ExpensedToDate money,
		EncumbranceAmount money,
		InvoiceAmount money,
		Source varchar(5))
end else begin
	drop index ExpenseDetails.FundDate 
	drop index ExpenseDetails.FundAccountNumber 
	drop index ExpenseDetails.FundAccountId 
	drop index ExpenseDetails.FundCategory 
end


Insert into dbo.ExpenseDetails (
		BusinessUnit, JournalID, JournalLine, CategoryId, AccountId, AccountNumber, UnpostSeqNumber, OrderDate, InvoiceDate, AccountingDate, 
		OrderTypeId, OrderType ,OrderNumber,PSVoucher, InvoiceNumber, RequisitionNumber, VendorName, RequestorName, 
		FundId, FundNumber, OrderAmount,ExpensedToDate, EncumbranceAmount, InvoiceAmount, Source
)
select 
		BusinessUnit, JournalID, JournalLine, CategoryId, AccountId, AccountNumber, UnpostSeqNumber, OrderDate, InvoiceDate, AccountingDate, 
		OrderTypeId, OrderType ,OrderNumber,PSVoucher, InvoiceNumber, RequisitionNumber, VendorName, RequestorName, 
		FundId, FundNumber, OrderAmount,ExpensedToDate, EncumbranceAmount, InvoiceAmount, Source
from #final

create index FundDate on ExpenseDetails (fundid,accountingdate)
create index FundAccountNumber on ExpenseDetails (fundid,accountNumber,accountingdate)
create index FundAccountId on ExpenseDetails (fundid,accountId,accountingdate)
create index FundCategory on ExpenseDetails (fundid,CategoryId,accountingdate)

end
GO